/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package app.bus;

import app.dao.DatabaseInteraction;
import app.entities.Product;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Tuan Long
 */
public class ProductModel {

    public static List getListProduct(int begin_rows, int end_rows) {
        List list = new ArrayList();
        String query = "SELECT [ProductID],[ProductName],[ProductPrice],[ProductImgURL] "
                + "FROM (SELECT ROW_NUMBER() OVER(ORDER BY ProductID) AS RowNumber, *FROM [Product] ) as pd_table "
                + "WHERE RowNumber >=" + begin_rows
                + "AND RowNumber <=" + end_rows;
        Connection cnn = DatabaseInteraction.getConnection();
        if (cnn != null) {
            ResultSet rs = DatabaseInteraction.getExcuteQuery(query);
            if (rs != null) {
                try {
                    Product p = null;
                    while (rs.next()) {
                        p = new Product();
                        p.setId(rs.getString("ProductID"));
                        p.setName(rs.getString("ProductName"));
                        p.setImgURL(rs.getString("ProductImgURL"));
                        p.setPrice(rs.getFloat("ProductPrice"));
                        list.add(p);
                    }
                } catch (SQLException ex) {
                    Logger.getLogger(ProductModel.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return list;
    }

    public static int getTotalProductRows() {
        int count = -1;
        String query = "SELECT COUNT(ProductID) as Total FROM [Product]";
        Connection cnn = DatabaseInteraction.getConnection();
        if (cnn != null) {
            ResultSet rs = DatabaseInteraction.getExcuteQuery(query);
            if (rs != null) {
                try {
                    while (rs.next()) {
                        count = rs.getInt("Total");
                        return count;
                    }
                } catch (SQLException ex) {
                    Logger.getLogger(ProductModel.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return count;
    }

    public static List<Product> getListAllProduct() {
        List<Product> list = new ArrayList<Product>();
        String query = "SELECT [ProductID],[ProductName],[ProductPrice],[ProductImgURL] "
                + "FROM Product";
        Connection cnn = DatabaseInteraction.getConnection();
        if (cnn != null) {
            ResultSet rs = DatabaseInteraction.getExcuteQuery(query);
            if (rs != null) {
                try {
                    Product p = null;
                    while (rs.next()) {
                        p = new Product();
                        p.setId(rs.getString("ProductID"));
                        p.setName(rs.getString("ProductName"));
                        p.setImgURL(rs.getString("ProductImgURL"));
                        p.setPrice(rs.getFloat("ProductPrice"));
                        list.add(p);
                    }
                } catch (SQLException ex) {
                    Logger.getLogger(ProductModel.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return list;
    }

    public static Product getProductByID(String productID) {
        Product p = null;
        String query = "SELECT [ProductID],[ProductName],[ProductPrice],[ProductImgURL],[ProductAmount],[ProductDes] "
                + "FROM [Product] "
                + "WHERE [ProductID]=" + productID;
        Connection cnn = DatabaseInteraction.getConnection();
        if (cnn != null) {
            ResultSet rs = DatabaseInteraction.getExcuteQuery(query);
            if (rs != null) {
                try {
                    while (rs.next()) {
                        p = new Product();
                        p.setId(rs.getString("ProductID"));
                        p.setName(rs.getString("ProductName"));
                        p.setPrice(rs.getFloat("ProductPrice"));
                        p.setImgURL(rs.getString("ProductImgURL"));
                        p.setDes(rs.getString("ProductDes"));
                        p.setAmount(rs.getInt("ProductAmount"));
                    }
                } catch (SQLException ex) {
                    Logger.getLogger(ProductModel.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return p;
    }

    public static List<Product> searchProductByName(String txt, int begin_rows, int end_rows) {
        List<Product> list = null;
        String query= "SELECT [ProductID],[ProductName],[ProductPrice],[ProductImgURL] "
                + "FROM (SELECT ROW_NUMBER() OVER(ORDER BY ProductID) AS RowNumber, "
                + "*FROM [Product] WHERE [Product].ProductName LIKE '%'+'" + txt + "'+'%' ) as pd_table "
                + "WHERE RowNumber >=" + begin_rows
                + "AND RowNumber <=" + end_rows;
                
        Connection cnn = DatabaseInteraction.getConnection();
        if(cnn!=null){
            list = new ArrayList<Product>();
            ResultSet rs = DatabaseInteraction.getExcuteQuery(query);
            if (rs != null) {
                try {
                    Product p = null;
                    while (rs.next()) {
                        p = new Product();
                        p.setId(rs.getString("ProductID"));
                        p.setName(rs.getString("ProductName"));
                        p.setImgURL(rs.getString("ProductImgURL"));
                        p.setPrice(rs.getFloat("ProductPrice"));
                        list.add(p);
                    }
                } catch (SQLException ex) {
                    Logger.getLogger(ProductModel.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return list;
    }

}
